﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text.RegularExpressions;
using System.Web;

namespace DesignLife.Admin.Business
{
    public class BusinessUser_Role
    {
        SqlConnection con = new SqlConnection();
        SqlCommand com = new SqlCommand();
        SqlDataAdapter da = new SqlDataAdapter();
        DataSet ds = new DataSet();
        private DataTable temp;
        private bool statusAction;
        private SqlDataReader Reader { get; set; }
        private int Success { get; set; }

        private SqlDataReader Query(string sql, params object[] parameters)
        {
            con.ConnectionString = ConfigurationManager.ConnectionStrings["Design_LifeConnection"].ConnectionString;
            com.Connection = con;
            com.CommandText = sql;
            com.CommandType = CommandType.Text;
            var reg = new Regex(@"@\w+");
            MatchCollection ps = reg.Matches(sql);
            if (ps.Count != parameters.Length) throw new Exception("paremeters not valid");
            int i = 0;
            foreach (Match item in ps)
            {
                foreach (Capture capture in item.Captures)
                {
                    com.Parameters.AddWithValue(capture.Value, parameters[i]);
                    i++;
                }
            }
            con.Open();
            return com.ExecuteReader();
        }

        private int Update(string sql, params object[] parameters)
        {
            try
            {
                con.ConnectionString = ConfigurationManager.ConnectionStrings["Design_LifeConnection"].ConnectionString;
                com.Connection = con;
                com.CommandText = sql;
                com.CommandType = CommandType.Text;
                var reg = new Regex(@"@\w+");
                MatchCollection ps = reg.Matches(sql);
                if (ps.Count != parameters.Length) throw new Exception("paremeters not valid");
                int i = 0;
                foreach (Match item in ps)
                {
                    foreach (Capture capture in item.Captures)
                    {
                        com.Parameters.AddWithValue(capture.Value, parameters[i]);
                        i++;
                    }
                }
                con.Open();
                return com.ExecuteNonQuery();
            }
            catch (Exception)
            {
                return 0;
            }
        }

        public void Close()
        {
            if (Reader != null)
            {
                Reader.Close();
                con.Close();
            }
            if(Success != 0)
            {
                con.Close();
            }
        }

        public DataTable getUserWithoutAdmin()
        {
            Close();
            temp = new DataTable();
            Reader = Query("SELECT dbo.Users.*, dbo.Roles.RoleName FROM dbo.Roles " +
                                "INNER JOIN dbo.Users ON dbo.Roles.RoleID = dbo.Users.IDRole" +
                                " WHERE NOT dbo.Roles.RoleName = 'Admin' and dbo.Users.Status = 1");
            temp.Load(Reader);
            return temp;
        }

        public DataTable getUserAll()
        {
            Close();
            temp = new DataTable();
            Reader = Query("SELECT dbo.Users.*, dbo.Roles.RoleName FROM dbo.Roles " +
                                "INNER JOIN dbo.Users ON dbo.Roles.RoleID = dbo.Users.IDRole");
            temp.Load(Reader);
            //con.Close();
            return temp;
        }


        public DataTable getRoleAll()
        {
            Close();
            temp = new DataTable();
            Reader = Query("SELECT * FROM Roles");
            temp.Load(Reader);
            return temp;
        }

        public DataTable getRoleWithoutAdmin()
        {
            Close();
            temp = new DataTable();
            Reader = Query("SELECT * FROM Roles WHERE NOT " +
                                "RoleName = 'admin' and Status = 1");
            temp.Load(Reader);
            return temp;
        }

        public bool AddUser(string userName, string password, string fullName, string email, string phone, int roleId)
        {
            Close();
            object[] param = { userName, password, fullName, email, phone, roleId };
            try
            {
                Success= Update("Insert Into Users (Username, [Password], Fullname, Email,Phone , IDRole)" +
                                 "values (@username, @password,@fullname, @email, @phone, @idRole)", param);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool UpdateUser(string fullName, string email, string phone, int roleId, bool status, int id)
        {
            Close();
            object[] param = {fullName, email, phone, roleId, status, id};
            try
            {
                Success = Update("Update Users set Fullname = @fullname," +
                          "Email = @email, Phone = @phone, IDRole = @idRole, Status = @status where UserID = @id", param);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool AddRole(string roleName)
        {
            Close();
            object[] param = { roleName };
            try
            {
                Success = Update("Insert Into Roles (RoleName) Values (@name)", param);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool UpdateRole(string roleName, bool status, int id)
        {
            Close();
            object[] param = { roleName, status, id };
            try
            {
                Success = Update("Update Roles set RoleName = @name, Status = @status where RoleID=@id", param);
                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}